<?php
require_once '../include/common.inc.php';
require_once '../include/excel.php';
error_reporting(E_ALL);
@ini_set("memory_limit",-1);

if (!checkAdmin()) {
    header("LOCATION:index.php");
    exit;
}

$menu_index = 1;
$page_title = 'JANSEN';
$guide_message = 'Product | Basic Infomation | Import Data';

getMyParam('importtype');

$error_str = '';
$upload_file = "";

// read the excel from the file  ::start
if ($importtype == '1' || $importtype == '2')
 {	
	$excel_type = array('application/vnd.ms-excel', 'application/octet-stream', 'application/x-msexcel');	

    if(strtolower(getExt($_FILES['upload_file']['name'])) == "xls" 
		&& $_FILES['upload_file']['size'] > 0)
	{
        $upload_file = $_FILES['upload_file']['tmp_name'];
		$tofile = JANSEN_ROOT.'upload/'.$_FILES['upload_file']['name'];
		$excel_ary = array();
		if(move_uploaded_file($upload_file, $tofile))
		{
			chmod($tofile,0777);
			$data = Excel::Read($tofile);
			$excel_ary = $data['cels'];
			unlink($tofile);
		}
		// Checked first Columns  === Web code nr
		if (empty($excel_ary) OR trim($excel_ary[0][0]) != 'Article #')
		{  
			$error_str .= "<div class='js_msg'>excel error in line 0: ".$excel_ary[0][0]."</div>";
		}
    }
	elseif ($_FILES['upload_file']['size'] > 0) 
	{
        $error_str .= "<div class='js_msg'>excel file error</div>";
    }
}
// read the excel from the file  ::end


// ==================== insert into database ==============================
if ($importtype == '1' && $error_str =="") 
{
	//$product_hava_ary   
	// $this_fid
	//Finishes_Category  $aryFinishesID = array();
	//Setting	$arySettingID = array();
	//Style	$aryStyleID = array();
	//Collection	$aryCollectionID = array();
	
	//$excel_sql_ary Common.admin的数组
	//excel_ary  excel 的数组
	//$sql_product_ary  二维数组 临时存放产品的数组
	//$sql_category_ary  //索引表
	//$cat_col_ary  类别数组 ;
	// $product_Category_ary 产品类别数组

	//取得产品 CODE为索引的产品 ID列表
	//GET the product code array
    $product_hava_ary = array();
    $sql = "SELECT `id`, `code` FROM `product`";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {
        $rs['code'] = trim(strtolower($rs['code']));
        $product_hava_ary[$rs['code']] = $rs['id'];
    }

  
	//取得类型值，从文件的名称获取. 取出类别ID号
	//get the file name from upload file ,and change to category ID; 
    $category_name = basename($_FILES['upload_file']['name'], ".xls");
	$category_name = str_replace('_',' ',trim($category_name));		

	$sql = "SELECT `id`,`name` FROM `category` WHERE `fid` = 0";
	$query = $db->query($sql);	
	$this_fid = 0;
	while($rs=$db->fetch_array($query))
	{
		$tmpname = preg_replace("/<[^>]+>/","",$rs["name"]);
	    if(strtolower(trim($tmpname)) == strtolower(trim($category_name)))
		{			
			$this_fid = $rs["id"];				
		}	
	}
	if (empty($this_fid)) {
        $error_str .= "<span class='js_msg'>Category error </span>";
    }
	
    $product_Category_ary = array();
    $product_Category_ary_arr = array();
    $sql = "SELECT `product_id`,`category_id`,`seq` FROM `product_category`";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {        
        $product_Category_ary[] = $rs['product_id'];
		$product_Category_ary_arr[$rs['product_id']][]=$rs['category_id'];
    }
	
	//Add by Leo 2010-07-07:
	//GET sub category ID string whith fid == $thisid;
	$cat_col_ary = array(); 
	$aryIn = get_category(0);
	GetProductCateIDAryByID($aryIn,$this_fid,&$cat_col_ary);
	$str_sub_cateid = GetCategoryTreeIDS($aryIn,$this_fid,false);
	
	//echo $str_sub_cateid."<br/>";
	
	//add by martin
	$category_col_ary = array(); 
	newGetProductCateIDAryByID($aryIn,$this_fid,&$category_col_ary);
	
	//Finishes
	$aryFinishesID = array();
	$sql = "SELECT `id`, `name` FROM `p_finish_set`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryFinishesID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//Setting
	$arySettingID = array();
	//$arySettingzhID=array();
	$sql = "SELECT `id`, `name`,`namezh` FROM `p_setting`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$arySettingID[strtolower(trim($rs['name']))] = $rs["id"];
		//$arySettingzhID[$rs['namezh']] = $rs["id"];
	}
	$arySettingID=array_unique($arySettingID);
	
	//Style
	$aryStyleID = array();
	$sql = "SELECT `id`, `name` FROM `p_style`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryStyleID[strtolower($rs['name'])] = $rs["id"];
	}
	
	// fabric 
	$fabric_cat_arr=array();
	$fabric_has_id=array();
	$sql = "SELECT `id`, `name` FROM `p_fabric_category` ";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query))
	{
		$fabric_cat_arr[trim(strtolower($rs['name']))] = $rs['id'];	
		$fabric_has_id=$rs['id'];
	}

	// leather 
	$leather_cat_arr=array();
	$leather_has_id=array();
	$sql = "SELECT `id`, `name`  FROM `p_leather_category` ";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) 
	{
		$leather_cat_arr[trim(strtolower($rs['name']))] = $rs['id'];	
		$leather_has_id[]=$rs['id'];	
	}
	
	
	//Collection
	$aryCollectionID = array();
	$sql = "SELECT `id`, `name` FROM `p_collection`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryCollectionID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//category 	
	$Category_All_Arr = array();
	$Category_All_NameId=array();
	$this_cate_id_ary = array();
	$sql = "SELECT `id`, `fid`,`code`,`name`,`namezh` FROM `category`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$Category_All_Arr[$rs['id']] = $rs["fid"];
		
		$key = trim($rs["fid"]).'_'.strtolower(trim($rs["name"]));
		$Category_All_NameId[$key]=$rs['id'];  // $this_fid.'_' 防止不同大类下同名的子类
		
		$key = trim($rs["fid"]).'_'.trim($rs["namezh"]);
		$Category_All_NameId[$this_fid.'_'.$rs["namezh"]]=$rs['id'];
		
		if($this_fid == $rs["fid"])
		{
			$this_cate_id_ary[] = $rs["id"];
		}
	}
	// 清空产品类目关系
	if(!empty($this_cate_id_ary))
	{
		$sql = "DELETE FROM `product_category` where category_id in(".implode(',',$this_cate_id_ary).")";
		$query = $db->query($sql);
	}

	//开始填充数据
	//首行代码
    foreach ($excel_ary[0] as $k=>$v) {		
        $v = (string)$v;
		if(empty($v)) continue;
		if( ! array_key_exists($v, $excel_sql_ary))
		{
			$error_str .= "<div class='js_msg'>excel error at title: " . $v . " not exists.</div>";
		}
		else
		{
        	$sql_product_ary[$k] = $excel_sql_ary[$v];
		}
    }	
	//首行代码结束	

    // ==================== ж end ==============================

	//	$excel_sql_ary 
    
	//if have data
    if (empty($excel_ary[1])) 
	{
        $error_str .= "<span class='js_msg'>excel error</span>";
    }
	$category_index = 0;
	//填充到数据库
    if ($error_str == "") 
	{
	    //ready insert database product code array
        $code_ary = array();	
		$category_index += 1;
		$columns=array();
		$excel_product_cate_id_ary = array();
	 
		foreach ($excel_ary as $k=>$v)
		{
			if ($k>0 && (isset($v[1]) && trim($v[1]) != ""))//if code no empty!	
			{
                $code = (string)trim($v[1]);				
				// checked and  insert to 	$code_ary					
				if (in_array(strtolower($code), $code_ary) && $code!="") 
				{ 
                    //$error_str .= "<div class='js_msg'>Code " . $code . " (" . $v[1] . ") have set.</div>";
                } 
				else	 
				{	
					//if has do insert/update mark here		
					$code_ary[] = strtolower($code);											
							
					$columns["pcode"]=$v[0];
					$columns["code"]= $code;
					$columns["bcode"]= $v[2];
					$columns["name"]=$v[3];
					$columns["hidden_flag"]=(strtolower($v[4])=='y')?0:1;
					$columns["new_flag"]=(strtolower($v[5])=='y')?1:0;
					$columns["fabric_flag"]= strtolower($v[6]) == 'y' ? 1:0;
					$columns["leather_flag"]= strtolower($v[7]) == 'y' ? 1:0;
					$columns["finish_id"]= $v[8];
					//Finishes
					if($columns['finish_id']!="")
					{
						$columns['finish_id'] = strtolower($columns['finish_id']);							
						if(!array_key_exists($columns['finish_id'],$aryFinishesID))
						{
							$sql4 = "INSERT INTO `p_finish_set` ( `name` , `createtime` ) VALUES ('".$columns['finish_id']."', '".date("Y-m-d H:i:s")."');";
							$db->query($sql4);
							$columns['finish_id'] = $aryFinishesID[$columns['finish_id']] = $db->insert_id();
						}
						else
						{
							$columns['finish_id']= $aryFinishesID[$columns['finish_id']];
						}
					}		
				   //Fabric/Leather	
				   //fabric category
				   $columns['fabric_cat']=0;
				   $fabric_cat = strtolower(trim(($v[9])));
					if( ! empty($fabric_cat) && array_key_exists($fabric_cat, $fabric_cat_arr))
					{		
						$columns['fabric_cat']= $fabric_cat_arr[$fabric_cat];
					}
					//leather category
				   $columns['leather_cat']=0;
				   $leather_cat = strtolower(trim(($v[9])));
					if( ! empty($leather_cat) && array_key_exists($leather_cat, $leather_cat_arr))
					{	
						$columns['leather_cat']= $leather_cat_arr[$leather_cat];
					}
					$columns["comment"]= strtoupper(substr($v[10],0,1)).substr($v[10],1);
					$columns["pic"]= strstr(strtolower($v[11]), ".jpg") ? $v[11] : "";	
					$columns["small_pic"]= strstr(strtolower($v[12]), ".jpg") ? $v[12] : "";
					$columns["price_2"]=$v[13];
					$columns["price_1"]=$v[14];
					$columns["price_3"]=$v[15];
					$columns["upcharge"]=$v[16];
					$columns["length"]=$v[17];
					$columns["width"]=$v[18];
					$columns["height"]=$v[19];
					$columns["cbm"]=$v[20];	
					$columns["width2"]=$v[21];
					$columns["length2"]=$v[22];
					$columns["height2"]=$v[23];					
					$columns["cuft"]=$v[24];	
					$columns["finishshown"]=$v[25];
					$columns["fabricshown"]=$v[26];
					$columns["leathershown"]=$v[27];
					$columns["marbleshown"]=$v[28];	
					$columns["accentshown"]=$v[29];
					$columns["priceshown"]=$v[30];				
					$columns["setting_id"]= $v[31];				
					$columns["setting_id2"]= $v[32];	
					$columns["style_id"]= $v[33];
					$columns["collection_id"]= $v[34];
					$columns["info"]=htmlentities($v[35],ENT_QUOTES,"UTF-8");
					$columns["sub_id"]= $v[36];
					$columns["namezh"]=$v[37];
					$columns["commentzh"]=$v[38];
					$columns["sub_idzh"]= $v[39];
					$columns["setting_idzh"]=$v[40];
					$columns["setting_idzh2"]=$v[41];
					$columns["style_idzh"]=$v[42];
					$columns["leathershownzh"]=$v[43];
					$columns["marbleshownzh"]=$v[44];
					$columns["infozh"]=htmlentities($v[45],ENT_QUOTES,"UTF-8");

				   //small pic
				/*$v[8] = strstr(strtolower($v[8]), ".jpg") ? $v[8] : "";
					if (!empty($v[8]) && !strpos($v[8], 's-') && $v[7] == $v[8] )
					 {
						if (strpos($v[8], "-s.")) 
						{
							$v[8] = str_replace("-s.", ".", $v[8]);
						}
						$v[8] = "s-" . $v[8];
					}
					$columns["small_pic"]= $v[8];*/
					
			      // echo $columns['leathershown']."_".$leather_id."<br/>";;
					
				//Style
				if($columns['style_id']!="")
				{
					$str_style = strtolower(trim($columns['style_id']));
					if(!array_key_exists($str_style,$aryStyleID) && $str_style!="")
					{
						$sql4 = "INSERT INTO `p_style` (`name`,`namezh`,`createtime`) VALUES('".$columns['style_id']."','".$columns['style_idzh']."','".date("Y-m-d H:i:s")."');";
						$db->query($sql4);
						$columns['style_id'] = $aryStyleID[$str_style] = $db->insert_id();
					}
					else
					{
						$columns['style_id']= $aryStyleID[$str_style];
					}
				}
				
				//Collection
				$columns['collection_id'] = strtolower(trim($columns['collection_id']));
				if($columns['collection_id']!="")
				{
					$str_collection = strtolower(trim($columns['collection_id']));
				 	if(!array_key_exists($str_collection, $aryCollectionID) AND ! empty($str_collection))
					{
						$sql4 = "INSERT INTO `p_setting` (`name`,`namezh`,`createtime`) VALUES('".$columns['collection_id']."','".$columns['collection_idzh']."','".date("Y-m-d H:i:s")."');";
						$db->query($sql4);
						$columns['collection_id'] = $aryCollectionID[$str_collection] = $db->insert_id();
					}
					else
					{
						$columns['collection_id'] = $aryCollectionID[$str_collection];
					}	
				}		
			
				//product 	insert or update
				if (array_key_exists(trim(strtolower($code)), $product_hava_ary))
				{		
					$update_sql_ary = array();							
					foreach($columns as $kc=>$vc)
					{
						if(in_array($kc, $filter_p_cols))
						{
							continue;
						}
					    $update_sql_ary[] = "`" . $kc . "` = '" . addslashes($vc) . "'";		
					}							
					$sql = "UPDATE `product` SET ".implode(",", $update_sql_ary) . ", `delete_flag`=0 WHERE `code` = '" . $code . "'";		
					$db->query($sql);						
				} 
				else 
				{
					$sql_ary_v = $sql_ary_value = array();
					foreach($columns as $kc=>$vc)
					{
						if(in_array($kc, $filter_p_cols))
						{
							continue;
						}
						$sql_ary_v[] = "`" . $kc . "`";
						$sql_ary_value[]= "'" . addslashes($vc) . "'";
					}	
					
					$sql = "INSERT INTO `product` (" .implode(",", $sql_ary_v) . ") VALUES ( 	" . implode(",", $sql_ary_value) . ")";
					$db->query($sql);
					$product_hava_ary[strtolower($code)] = $db->insert_id();
				}
				//setting
				$SettingHas = $arr = $arrzh = array();					
				if(trim($columns['setting_id'])!="")
				{
					$arr[] = $columns['setting_id'];
				}				
				if(trim($columns['setting_id2'])!="")
				{
					$arr[] = $columns['setting_id2'];
				}				
				if(trim($columns['setting_idzh'])!="")
				{
					$arrzh[] = $columns['setting_idzh'];
				}				
				if(trim($columns['setting_idzh2'])!="")
				{
					$arrzh[] = $columns['setting_idzh2'];
				}
				foreach($arr as $kk=>$vv)
				{
					$tmp = strtolower(trim($vv));
					$vv= htmlentities($tmp);
					if(!array_key_exists($tmp,$arySettingID) && $tmp!="")
					{				
						$vv =ucwords($vv);
						$sql4 = "INSERT INTO `p_setting` (`name`,`namezh`,`createtime`) VALUES('".$vv."','".  $arrzh[$kk]."','".date("Y-m-d H:i:s")."');";
						$db->query($sql4);
						$setting_id = $db->insert_id();
						$SettingHas[] = $setting_id;
						$arySettingID[$tmp] = $setting_id;
						//$columns['setting_id'] = $arySettingID[$columns['setting_id']] = $db->insert_id();
					}
					else
					{
						//$columns['setting_id'] = $arySettingID[$columns['setting_id']];
						if(isset($arySettingID[$tmp]))
						$SettingHas[] = $arySettingID[$tmp];
					}
				}
															
				// delete old data and insert latest data;
				$sqld = "DELETE  FROM  `product_p_setting` WHERE `product_id`=".$product_hava_ary[strtolower($code)];							
				$db->query($sqld);								
				foreach($SettingHas as $vss)
				{          
				   $sqld = "INSERT INTO `product_p_setting` (`setting_id`,`product_id`, `seq`) VALUES ('".$vss."','".$product_hava_ary[strtolower($code)]."','0')";	
				   $db->query($sqld);
				} 
				
				//sub Category
				//$columns["sub_id"] = trim($columns["sub_id"] ) == "" ? $this_fid : $category_col_ary[$columns["sub_id"]];
				$product_cate_id_ary = array();
				if( ! empty($columns["sub_id"]) or  ! empty($columns["sub_idzh"]))
				{
					$cate_str_ary = explode('|', $columns['sub_id']);
					$cate_str_zh_ary = ! empty($columns['sub_idzh']) ? explode('|', $columns['sub_idzh']) : array();
					foreach($cate_str_ary as $ck => $cv)
					{
						$tmp_key = $this_fid.'_'.strtolower(trim($cv));
						if(array_key_exists($tmp_key, $Category_All_NameId))
						{
							$product_cate_id_ary [] = $Category_All_NameId[$tmp_key];
						}
						else
						{
							$cvzh = isset($cate_str_zh_ary[$ck])?$cate_str_zh_ary[$ck]:'';
							$sql = "INSERT INTO `category` (`fid`,`name`,`namezh`) VALUES('".$this_fid."','".$cv."','".$cvzh."');";
							$db->query($sql);
							$product_cate_id_ary[] = $Category_All_NameId[$tmp_key] = $db->insert_id();
						}
					}
				}
				else
				{
					$product_cate_id_ary[] = $this_fid;
				}	
							
				if(!empty($product_cate_id_ary))
				{
					foreach($product_cate_id_ary as $vb)
					{												   
						$sqlsub = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($code)] . "', '" . $vb . "', '$category_index')";						 				
						$db->query($sqlsub);
						$category_index += 1;	
					}
					$excel_product_cate_id_ary = array_merge($excel_product_cate_id_ary, $product_cate_id_ary);
				}	
				
				// insert end		
                }
            }
        } //196: foreach ($excel_ary as $k=>$v)
		
		 
		// UPDATE category
		$excel_product_cate_id_ary = array_unique($excel_product_cate_id_ary);
		if( ! empty($excel_product_cate_id_ary))
		{
			$sql = "UPDATE `category` SET publish = 1 WHERE id IN(".implode(',',$excel_product_cate_id_ary).");";		 				
			$db->query($sql);
			
			$sql = "UPDATE `category` SET publish = 0 WHERE id NOT IN(".implode(',',$excel_product_cate_id_ary).") AND fid > 0 AND fid =".$this_fid.";";		 				
			$db->query($sql);
			
			category_cache();
		}
		
		
		$sql="select id,code from product where id in(select  product_id from `product_category` where category_id in (".$str_sub_cateid."))";
		$query = $db->query($sql);
		$product_in_category = array();
		while($rs = $db->fetch_array($query)) 
		{
			$product_in_category[$rs["code"]] = $rs["id"];
		}
		
		foreach($product_in_category as $product_code => $product_id)
		{
			if(!in_array(strtolower($product_code),$code_ary))
			{
				$sql3 = "update `product` set hidden_flag = 1 where `id` = $product_id";
				$db->query($sql3);
			}
		}
    }
	
	//193: if ($error_str == "")	
    // ================= end import excel ==========================
   

} elseif ($importtype == '2' && $error_str == "") 
{

    // ============================= import inventory data start==========================
    if ($excel_ary[0][1] != "Qty") {
        $error_str = "<div class='js_msg'>This isn't a inventory excel !</div>";
    } else {
        //if ($excel_ary[0][2] == "HKWHSP(USD)" && $excel_ary[0][3] == "HKWHSP(HKD)") {
		if ($excel_ary[0][2] == "Special Price" && $excel_ary[0][3] == "Special Price2") 
		{
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0])  && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0) {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }
                    if (!isset($v[3])) {
                        $v[3] = 0;
                    }
                    $v[0] = strtolower($v[0]);
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp1'] = $v[2];
                    $code_ary[$v[0]]['sp2'] = $v[3];
					$code_ary[$v[0]]['inventory_name'] = trim($v[4]);
					$inventory_name_ary[] = trim($v[4]);
                }
            }

            //$inventory_name_ary = array(0=>"Indonesia Warehouse",1=>"Singapore Show Container");
            //echo "<pre>";print_r($code_ary);//exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
	
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);//code X
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];   //дڵcode飬жЩcodeڵ
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    //$id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$id_code_ary[$rs['id']]['inventory_name']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }

                
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {
                    // жЩƷĳinventoryqtyѴ
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp1'] . "', `special_price2` ='" . $v['sp2'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "';";			
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`, `special_price2`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp1'] . "', '" . $v['sp2'] . "');";
                        $query = $db->query($sql);
                    }
                }
            }

            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

        } else 
		{
            // ============ ݸʽΪ start ========================
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0]) && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0 && $v[3] != '') {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }

                    $v[0] = strtolower($v[0]);
                    $v[2] = strtolower($v[2]) == 'nil' ? 0 : $v[2];
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp'] = $v[2];
                    $code_ary[$v[0]]['inventory_name'] = trim($v[3]);
                    $inventory_name_ary[] = trim($v[3]);
                }
            }

            //echo "<pre>";print_r($inventory_name_ary);exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];  
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }
               
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {                 
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "'";
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp'] . "')";	
                        $query = $db->query($sql);
                    }
		        }
            }
            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

            // ============================= import inventory data end==========================
        }
    }




} elseif ($importtype == '3') {
    if(strtolower(getExt($_FILES['upload_file']['name'])) == "zip" && $_FILES['upload_file']['size'] > 0 && ($_FILES['upload_file']['type'] == "application/x-zip-compressed")){

        require_once '../include/zip.class.php';

        $upload_file = $_FILES['upload_file']['tmp_name'];
        $pic_file_name = "../zh/attachments/" . $_FILES['upload_file']['name'];
        if (!move_uploaded_file($_FILES['upload_file']['tmp_name'], $pic_file_name)) {          
            $error_str = 'upload zip file failed<br>';
        } else {
            $tmp_file2 = '../zh/attachments/';
            $z=new Zip;
            $z->Extract($pic_file_name, $tmp_file2);
            @unlink($pic_file_name);
        }
    } elseif ($_FILES['upload_file']['size'] > 0) {
        $error_str .= "<div class='js_msg'>picture file error</div>";
    }
}


if ($upload_file != "" && $error_str == '') {
    $error_str = "<span class='red_font'>Import data Succeed</span>";
}

/*
$debug = 1;
debuginfo();
echo "<pre>";
print_r($db->sql_ary);
*/

include_once(JANSEN_ROOT . 'admin/menu_list.php');
require_once PrintEot('a_header');
require_once PrintEot('a_p_import_excel');
require_once PrintEot('a_footer');
?>
